﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Web.Script.Serialization;
using WebMatrix.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ClassName
/// </summary>
public class CustomerManager
{
    public CustomerManager()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    
    public static int CreateCustomer(Customer c){
        
        string sql = @"INSERT INTO Customer(
            CustomerID,
            CustomerClassification,
            CustomerStatus,
            Region,
            Solicitor,
            Campaign,
            ReferredBy,
            RelationshipToReferror,
            Country,
            FName,
            MName,
            LName,
            Gender,
            MonthArrived,
            Nationality,
            Bdate,
            PlaceOfBirth,
            MotherMName,
            CivilStatus,
            IDNumber,
            IDType,
            Email,
            CurrMailAdd,
            CurrMailCity,
            CurrMailState,
            CurrMailCountry,
            CurrMailZip,
            PermAdd,
            PermAddCity,
            PermAddState,
            PermAddCountry,
            PermAddZip,
            SrcOfFunds,
            Employment,
            Designation,
            Employer,
            NatureOfWork,
            WorkPhone,
            BusAdd,
            BusAddCity,
            BusAddState,
            BusAddCountry,
            BusAddZip,
            ModeOfRemit,
            CallFrequency,
            CargoFrequency,
            RemitFrequency,
            ModeOfCall,
            AllowContact,
            PrefModeOfComm,
            PrefCallTime,
            PreffCallDay,
            YearArrived,
            MobileNo,
            LandLineNo,
            DateCreated,
            DateUpdated,
            Notes,
            HasDuplicate,
            CustomerRegion)
            VALUES(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@47,@48,@49,@50,@51,@52,@53,@54,@55,@56,@57,@58,@59)";
           List<object> param = new List<object>();
           
            param.Add(c.CustomerID);
            param.Add(c.CustomerClassification);
            param.Add(c.CustomerStatus);
            param.Add(c.Region);
            param.Add(c.Solicitor);
            param.Add(c.Campaign);
            param.Add(c.ReferredBy);
            param.Add(c.RelationshipToReferror);
            param.Add(c.Country);
            param.Add(c.FName);
            param.Add(c.MName);
            param.Add(c.LName);
            param.Add(c.Gender);
            param.Add(c.MonthArrived);
            param.Add(c.Nationality);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.Bdate));
            param.Add(c.PlaceOfBirth);
            param.Add(c.MotherMName);
            param.Add(c.CivilStatus);
            param.Add(c.IDNumber);
            param.Add(c.IDType);
            param.Add(c.Email);
            param.Add(c.CurrMailAdd);
            param.Add(c.CurrMailCity);
            param.Add(c.CurrMailState);
            param.Add(c.CurrMailCountry);
            param.Add(c.CurrMailZip);
            param.Add(c.PermAdd);
            param.Add(c.PermAddCity);
            param.Add(c.PermAddState);
            param.Add(c.PermAddCountry);
            param.Add(c.PermAddZip);
            param.Add(c.SrcOfFunds);
            param.Add(c.Employment);
            param.Add(c.Designation);
            param.Add(c.Employer);
            param.Add(c.NatureOfWork);
            param.Add(c.WorkPhone);
            param.Add(c.BusAdd);
            param.Add(c.BusAddCity);
            param.Add(c.BusAddState);
            param.Add(c.BusAddCountry);
            param.Add(c.BusAddZip);
            param.Add(c.ModeOfRemit);
            param.Add(c.CallFrequency);
            param.Add(c.CargoFrequency);
            param.Add(c.RemitFrequency);
            param.Add(c.ModeOfCall);
            param.Add(c.AllowContact);
            param.Add(c.PrefModeOfComm);
            param.Add(c.PrefCallTime);
            param.Add(c.PreffCallDay);
            param.Add(c.YearArrived);
            param.Add(c.MobileNo);
            param.Add(c.LandLineNo);
            param.Add(c.DateCreated);
            param.Add(c.DateUpdated);
            param.Add(c.Notes);
            param.Add(c.HasDuplicate);
            //get CustomerRegion 
            var custRegion = RefDataManager.GetCountryRegion(c.Country);
            param.Add(custRegion.ParentRefID);

          var result = DBManager.Execute(sql,param.ToArray());
          return result;
          
    }
    
    public static int UpdateCustomer(Customer c){
       string sql=@"UPDATE Customer SET
                    CustomerClassification=@0,
                    CustomerStatus=@1,
                    Region=@2,
                    Solicitor=@3,
                    Campaign=@4,
                    ReferredBy=@5,
                    RelationshipToReferror=@6,
                    Country=@7,
                    FName=@8,
                    MName=@9,
                    LName=@10,
                    Gender=@11,
                    MonthArrived=@12,
                    Nationality=@13,
                    Bdate=@14,
                    PlaceOfBirth=@15,
                    MotherMName=@16,
                    CivilStatus=@17,
                    IDNumber=@18,
                    IDType=@19,
                    Email=@20,
                    CurrMailAdd=@21,
                    CurrMailCity=@22,
                    CurrMailState=@23,
                    CurrMailCountry=@24,
                    CurrMailZip=@25,
                    PermAdd=@26,
                    PermAddCity=@27,
                    PermAddState=@28,
                    PermAddCountry=@29,
                    PermAddZip=@30,
                    SrcOfFunds=@31,
                    Employment=@32,
                    Designation=@33,
                    Employer=@34,
                    NatureOfWork=@35,
                    WorkPhone=@36,
                    BusAdd=@37,
                    BusAddCity=@38,
                    BusAddState=@39,
                    BusAddCountry=@40,
                    BusAddZip=@41,
                    ModeOfRemit=@42,
                    CallFrequency=@43,
                    CargoFrequency=@44,
                    RemitFrequency=@45,
                    ModeOfCall=@46,
                    AllowContact=@47,
                    PrefModeOfComm=@48,
                    PrefCallTime=@49,
                    PreffCallDay=@50,
                    YearArrived=@51,
                    MobileNo=@52,
                    LandLineNo=@53,
                    DateUpdated=@54,
                    Notes=@55,
                    CustomerRegion=@56
                     WHERE CustomerID=@57";
        
        
         List<object> param = new List<object>();
            param.Add(c.CustomerClassification);
            param.Add(c.CustomerStatus);
            param.Add(c.Region);
            param.Add(c.Solicitor);
            param.Add(c.Campaign);
            param.Add(c.ReferredBy);
            param.Add(c.RelationshipToReferror);
            param.Add(c.Country);
            param.Add(c.FName);
            param.Add(c.MName);
            param.Add(c.LName);
            param.Add(c.Gender);
            param.Add(c.MonthArrived);
            param.Add(c.Nationality);
            param.Add(UtilityManager.GetDefaultDateIfNull(c.Bdate));
            param.Add(c.PlaceOfBirth);
            param.Add(c.MotherMName);
            param.Add(c.CivilStatus);
            param.Add(c.IDNumber);
            param.Add(c.IDType);
            param.Add(c.Email);
            param.Add(c.CurrMailAdd);
            param.Add(c.CurrMailCity);
            param.Add(c.CurrMailState);
            param.Add(c.CurrMailCountry);
            param.Add(c.CurrMailZip);
            param.Add(c.PermAdd);
            param.Add(c.PermAddCity);
            param.Add(c.PermAddState);
            param.Add(c.PermAddCountry);
            param.Add(c.PermAddZip);
            param.Add(c.SrcOfFunds);
            param.Add(c.Employment);
            param.Add(c.Designation);
            param.Add(c.Employer);
            param.Add(c.NatureOfWork);
            param.Add(c.WorkPhone);
            param.Add(c.BusAdd);
            param.Add(c.BusAddCity);
            param.Add(c.BusAddState);
            param.Add(c.BusAddCountry);
            param.Add(c.BusAddZip);
            param.Add(c.ModeOfRemit);
            param.Add(c.CallFrequency);
            param.Add(c.CargoFrequency);
            param.Add(c.RemitFrequency);
            param.Add(c.ModeOfCall);
            param.Add(c.AllowContact);
            param.Add(c.PrefModeOfComm);
            param.Add(c.PrefCallTime);
            param.Add(c.PreffCallDay);
            param.Add(c.YearArrived);
            param.Add(c.MobileNo);
            param.Add(c.LandLineNo);
            param.Add(c.DateUpdated);
            param.Add(c.Notes);
            //get CustomerRegion 
            var custRegion = RefDataManager.GetCountryRegion(c.Country);
            param.Add(custRegion.ParentRefID);
            param.Add(c.CustomerID);
       return DBManager.Execute(sql,param.ToArray()); 
    }
    
    public static int DeleteCustomer(object[] cust){
        string sql="DELETE FROM Customer WHERE CustomerID=@0";
        return DBManager.Execute(sql,cust);
    }
    
    public static int CheckDuplicate(Customer cust){
        StringBuilder sb = new StringBuilder();
        sb.Append("SELECT CustomerID FROM Customer WHERE charindex(@0,upper(FName))>0  AND charindex(@1,upper(LName))>0 "); 
        string sql = "SELECT Count(*) FROM Customer WHERE FName=@0 AND LName=@1";
        List<object> param = new List<object>();
        param.Add(cust.FName);
        param.Add(cust.LName);
      
        int ctr=2;
        bool flagForDups = false;
        if(cust.Bdate!=null){
            sb.Append(string.Format(" AND Bdate=@{0}",ctr++));
            param.Add(cust.Bdate);
            flagForDups=true;
        }
        
        if(cust.MName!=null && cust.MName!=""){
            sb.Append(string.Format(" AND charindex(@{0},upper(MName))>0",ctr++));
            param.Add(cust.MName);
        }
        
        if(cust.Region!=null && cust.Region!=""){
            sb.Append(string.Format(" AND Region=@{0}",ctr++));
            param.Add(cust.Region);
        }
        
       if(cust.Country!=null && cust.Country!=""){
            sb.Append(string.Format(" AND Country=@{0}",ctr++));
            param.Add(cust.Country);
        }
        
       if(cust.CurrMailState!=null && cust.CurrMailState!=""){
            sb.Append(string.Format(" AND CurrMailState=@{0}",ctr++));
            param.Add(cust.CurrMailState);
        }
        
        if(cust.CurrMailZip!=null && cust.CurrMailZip!=""){
            sb.Append(string.Format(" AND CurrMailZip=@{0}",ctr++));
            param.Add(cust.CurrMailZip);
        }
        
        if(cust.MobileNo!=null && cust.MobileNo!="||"){
             sb.Append(string.Format(" AND MobileNo=@{0}",ctr++));
            param.Add(cust.MobileNo);
        }
        
        if(cust.LandLineNo!=null && cust.LandLineNo!="||"){
             sb.Append(string.Format(" AND LandLineNo=@{0}",ctr++));
            param.Add(cust.LandLineNo);
        }
        
        if(cust.IDType!=null && cust.IDType!=""){
            sb.Append(string.Format(" AND IDType=@{0}",ctr++));
            param.Add(cust.IDType);
        }
       
        if(cust.IDNumber!=null && cust.IDNumber!=""){
            sb.Append(string.Format(" AND IDNumber=@{0}",ctr++));
            param.Add(cust.IDNumber);
        }
        
        var query =DBManager.Query(sb.ToString(),param.ToArray());
        //var db = Database.Open("CCMS");
        //var count= db.QueryValue(sb.ToString(),param.ToArray());
       int count=query.Count;
        if(flagForDups){
            return count;
        }
        
        return 0;;
    }
    
    public static IList<dynamic> SearchCustomer(Customer cust){
        string sql=@"SELECT CustomerID,
                            FName,
                            MName,
                            LName,
                            CustomerStatus,
                            CurrMailCountry,
                            CurrMailState,
                            CurrMailZip,
                            BDate,
                            IDType, 
                            Solicitor,  
                            MobileNo,
                            LandLineNo,
                            ReferredBy 
                            FROM Customer WHERE charindex(@0,upper(FName))>0  AND charindex(@1,upper(LName))>0"; 
        //check if the customer being searched has cash card applications
        
        
        
        
        //chech which search criteria are present then concat it with the main sql search statement
        List<object> param = new List<object>();
        int ctr=2;
        param.Add(cust.FName);
        param.Add(cust.LName);
        //param.Add(cust.Bdate);
       
        StringBuilder sb = new StringBuilder();
        sb.Append(sql);
        if(cust.Bdate!=null){
            sb.Append(string.Format(" AND Bdate=@{0}",ctr++));
            param.Add(cust.Bdate);
        }
        
        if(cust.MName!=null && cust.MName!=""){
            sb.Append(string.Format(" AND charindex(@{0},upper(MName))>0",ctr++));
            param.Add(cust.MName);
        }
        
        if(cust.Region!=null && cust.Region!=""){
            sb.Append(string.Format(" AND Region=@{0}",ctr++));
            param.Add(cust.Region);
        }
        
        if(cust.Country!=null && cust.Country!=""){
            sb.Append(string.Format(" AND Country=@{0}",ctr++));
            param.Add(cust.Country);
        }
        
       if(cust.CurrMailState!=null && cust.CurrMailState!=""){
            sb.Append(string.Format(" AND CurrMailState=@{0}",ctr++));
            param.Add(cust.CurrMailState);
        }
        
        if(cust.CurrMailZip!=null && cust.CurrMailZip!=""){
            sb.Append(string.Format(" AND CurrMailZip=@{0}",ctr++));
            param.Add(cust.CurrMailZip);
        }
        
        if(cust.MobileNo!=null && cust.MobileNo!="||"){
             sb.Append(string.Format(" AND MobileNo=@{0}",ctr++));
            param.Add(cust.MobileNo);
        }
        
        if(cust.LandLineNo!=null && cust.LandLineNo!="||"){
             sb.Append(string.Format(" AND LandLineNo=@{0}",ctr++));
            param.Add(cust.LandLineNo);
        }
        
        if(cust.IDType!=null && cust.IDType!=""){
            sb.Append(string.Format(" AND IDType=@{0}",ctr++));
            param.Add(cust.IDType);
        }
       
        if(cust.IDNumber!=null && cust.IDNumber!=""){
            sb.Append(string.Format(" AND IDNumber=@{0}",ctr++));
            param.Add(cust.IDNumber);
        }
        
        var query = DBManager.Query(sb.ToString(),param.ToArray());
        return query;
        
    }
    
    
   public static IList<dynamic> SearchCustomer2(Customer cust){
        string sql=@"SELECT CustomerID,
                            FName,
                            MName,
                            LName,
                            CustomerStatus,
                            CurrMailCountry,
                            CurrMailState,
                            CurrMailZip,
                            BDate,
                            IDType, 
                            Solicitor,  
                            MobileNo,
                            LandLineNo,
                            ReferredBy 
                            FROM Customer";  
        //check if the customer being searched has cash card applications
    
        //WHERE charindex(@0,upper(FName))>0  AND charindex(@1,upper(LName))>0";
        int paramCtr=0;
        StringBuilder sb = new StringBuilder();
        sb.Append(sql);
        Dictionary<string,dynamic> p = new Dictionary<string,dynamic>();
    
        if(cust.FName!=null){
            p.Add("FName",cust.FName);
            paramCtr++;
        }
        
        if(cust.LName!=null){
            p.Add("LName",cust.LName);
            paramCtr++;
        }
        
        if(cust.Bdate!=null){
            p.Add("Bdate",cust.Bdate);
            paramCtr++;
        }
        
        if(cust.MName!=null && cust.MName!=""){
            p.Add("MName",cust.MName);
            paramCtr++;
        }
        
        if(cust.Region!=null && cust.Region!=""){
           p.Add("Region",cust.Region);
           paramCtr++;
        }
        
        if(cust.Country!=null && cust.Country!=""){
            p.Add("Country",cust.Country);
            paramCtr++;
        }
        
       if(cust.CurrMailState!=null && cust.CurrMailState!=""){
            p.Add("CurrMailState",cust.CurrMailState);
            paramCtr++;
        }
        
        if(cust.CurrMailZip!=null && cust.CurrMailZip!=""){
            p.Add("CurrMailZip",cust.CurrMailZip);
            paramCtr++;
        }
        
        if(cust.MobileNo!=null && cust.MobileNo!="||"){
            p.Add("MobileNo",cust.MobileNo);
            paramCtr++;
        }
        
        if(cust.LandLineNo!=null && cust.LandLineNo!="||"){
             p.Add("LandLIneNo",cust.LandLineNo);
             paramCtr++;
        }
        
        if(cust.IDType!=null && cust.IDType!=""){
            p.Add("IDType",cust.IDType);
            paramCtr++;
        }
       
        if(cust.IDNumber!=null && cust.IDNumber!=""){
            p.Add("IDNumber",cust.IDNumber);
            paramCtr++;
        }
        
        //loop through the params
        if(paramCtr>0){
            int ctr=0;
            string cond=string.Empty;
            foreach(KeyValuePair<string,dynamic> pair in p){
                if(ctr==0){
                    cond=" WHERE";
                }else{
                    cond=" AND";
                }
                if((string.Compare(pair.Key,"FName")==0) || (string.Compare(pair.Key,"MName")==0) || (string.Compare(pair.Key,"LName")==0)){
                     sb.Append(cond).Append(string.Format(" {0} LIKE '{1}%'",pair.Key,pair.Value));
                }else{
                     sb.Append(cond).Append(string.Format(" {0}='{1}'",pair.Key,pair.Value));
                }
               
                
                ctr++;
            }
        }
        
        
        var query = DBManager.Query(sb.ToString(),null);
        return query;
        
    }
    
    
    
    public static IList<dynamic> GetAllCustomers(){
         string sql = "SELECT * FROM Customer";
         var query = DBManager.Query(sql,null);
         return query;
    }
    
    public static string GetNextCustomerID(string region){
        if(region!=null){
            var sql = "SELECT Max(CSequence) as NextSeq FROM Uniques WHERE Region=@0";
            object[] param = new object[1]{region};
            var query = DBManager.Query(sql,param);
            int nextID=0;
            bool flag=false;
            foreach(var id in query){
                var nid=id.NextSeq;
                if(nid==null){
                    flag=true;
                    //insert new region and set sequence numbers to 0
                    object[] newData = new object[3]{region,0,0}; 
                    DBManager.Execute("INSERT INTO Uniques(Region,CSequence,CCSequence) VALUES(@0,@1,@2)",newData);
                }else{
                    nextID =(int)nid;
                }
            }
    
                
                  
            string seq = string.Format("CC-{0}-{1:d9}",region,++nextID);
            object[] param2=null;
            var usql=string.Empty;
            if(flag){
               usql= "UPDATE Uniques SET CSequence=@0,CCSequence=@1,CustomerID=@2 WHERE Region=@3";    
                 param2 = new object[4]{nextID,0,seq,region};
            }else{
                usql = @"INSERT INTO Uniques(Region,CSequence,CCSequence,CustomerID)
                         VALUES(@0,@1,@2,@3)";
                 param2 = new object[4]{region,nextID,0,seq};
            }
            
            
            //object[] param2 = new object[4]{region,nextID,0,seq};
            var uquery = DBManager.Execute(usql,param2);
            
            return seq;     
        }else{
            return string.Empty;
        }
       
    }
    
    public static dynamic GetCustomer(string customerID){
            var sql = @"SELECT * FROM Customer WHERE CustomerID=@0";
            object[] param = new object[1]{customerID};
            var cList = DBManager.Query(sql,param);
            if(cList.Count>0){
                return cList[0];
            }
            
            return null;
    }
    
    public static IList<dynamic> GetCustomerCashCards(string customerID){
        //get Cash Cards for Customer
        var ccSql = "SELECT * FROM CustomerCashCard WHERE CustomerID=@0";
        object[] param2 = new object[1]{customerID};
        var ccList = DBManager.Query(ccSql,param2);
        return ccList;
    }
    
    public static IList<dynamic> GetReferredCustomers(string customerID){
        var sql = "SELECT CustomerID FROM Customer WHERE ReferredBy=@0";
        object[] param = new object[1]{customerID};
        var cList = DBManager.Query(sql,param);
        return cList;
    }
    
    public static int UpdateCustomerDetailsForCC(Customer c){
        var sql = @"UPDATE Customer 
                   SET Bdate=@0,
                   PlaceOfBirth=@1,
                   CivilStatus=@2,
                   IDType=@3,
                   IDNumber=@4,
                   CustomerStatus=@5,
                   SrcOfFunds=@6,
                   Employer=@7,
                   NatureOfWork=@8,
                   DateUpdated=@9
                   WHERE CustomerID=@10";
        List<object> param = new List<object>();
        param.Add(UtilityManager.GetDefaultDateIfNull(c.Bdate));
        param.Add(c.PlaceOfBirth);
        param.Add(c.CivilStatus);
        param.Add(c.IDType);
        param.Add(c.IDNumber);
        param.Add(c.CustomerStatus);
        param.Add(c.SrcOfFunds);
        param.Add(c.Employer);
        param.Add(c.NatureOfWork);
        param.Add(c.DateUpdated);
        param.Add(c.CustomerID);
        var result = DBManager.Execute(sql,param.ToArray());
        return result;
    }
    
    
    
    
}
